package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.FundsDetailEntity;
import com.b2c.entity.funds.LedgerDayModel;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Repository
public class FundsDetailRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }
    @Transactional
    public PagingResponse<FundsDetailEntity> getFundsDetailList(Integer pageIndex, Integer pageSize,Integer type,String source,String sourceNo,String createDate){
        StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS * FROM erp_funds_detail WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (type != null) {
            sql.append(" AND `type` =? ");
            params.add(type);
        }
        if(StringUtils.isEmpty(source)==false){
            sql.append(" AND source =? ");
            params.add(source);
        }
        if(StringUtils.isEmpty(sourceNo)==false){
            sql.append(" AND sourceNo =? ");
            params.add(sourceNo);
        }
        if(StringUtils.isEmpty(createDate)==false){
            sql.append(" AND createDate =? ");
            params.add(createDate);
        }
        sql.append(" ORDER BY id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<FundsDetailEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(FundsDetailEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }


    public ResultVo<Long> create(FundsDetailEntity fEntity) {
         /*****1、添加order*****/
         StringBuilder orderInsertSQL = new StringBuilder();
         orderInsertSQL.append("INSERT INTO erp_funds_detail");
         orderInsertSQL.append(" SET ");
         orderInsertSQL.append(" `type`=?,");
         orderInsertSQL.append(" `source`=?,");
         orderInsertSQL.append(" shopId=?,");
         orderInsertSQL.append(" amount=?,");
         orderInsertSQL.append(" sourceNo=?,");
         orderInsertSQL.append(" remark=?,");
         orderInsertSQL.append(" createDate=?");
         orderInsertSQL.append(",billDate=?");
         try {
             KeyHolder keyHolder = new GeneratedKeyHolder();
             jdbcTemplate.update(new PreparedStatementCreator() {
                 @Override
                 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                     PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                     ps.setInt(1, fEntity.getType());
                     ps.setString(2, fEntity.getSource());
                     ps.setInt(3, fEntity.getShopId());
                     ps.setDouble(4, fEntity.getAmount());
                     ps.setString(5, fEntity.getSourceNo());
                     ps.setString(6, fEntity.getRemark());
                     ps.setString(7, DateUtil.getCurrentDate());
                     ps.setString(8, fEntity.getCreateDate());
                     return ps;
                 }
             }, keyHolder);
 
             Long orderIdNew = keyHolder.getKey().longValue();
             return new ResultVo<>(EnumResultVo.SUCCESS, orderIdNew);
            }catch(Exception e){
                return new ResultVo<>(EnumResultVo.Fail, "异常：" + e.getMessage());
            }
    }


    @Transactional
    public List<LedgerDayModel> getLedgerDayReport(String startDate, String endDate) {
        Date startDate2 = DateUtil.stringtoDate(startDate,"yyyy-MM-dd");
        Date endDate2 = DateUtil.stringtoDate(endDate,"yyyy-MM-dd");
        int days = DateUtil.differentDays(startDate2,endDate2);

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  ? AS `date` ");
        sql.append(",IFNULL((SELECT SUM(totalAmount) FROM erp_invoice WHERE (transType='Purchase' OR transType='OrderDaiFa') AND billStatus=1 AND billDate= ?),0) AS purYF");
        sql.append(",IFNULL((SELECT SUM(totalAmount) FROM erp_invoice WHERE (transType='PUR_RETURN' OR transType='DaiFaRefund') AND billStatus=1 AND billDate= ?),0) AS purRefundYS");
        sql.append(",IFNULL((SELECT SUM(amount) FROM  erp_funds_detail WHERE `source`='PURCHASE' AND billDate= ?),0) AS purSF");
        sql.append(",IFNULL((SELECT SUM(amount) FROM  erp_funds_detail WHERE `source`='PURREFUND' AND billDate= ?),0) AS purRefundSS");
        
        sql.append(",IFNULL((SELECT SUM(totalAmount) FROM erp_order_send WHERE DATE_FORMAT(deliveryTime,'%Y-%m-%d') = ?),0) AS saleAmount");
        sql.append(",IFNULL((SELECT SUM(refundAmount) FROM erp_order_send_return WHERE DATE_FORMAT(receiveTime,'%Y-%m-%d') = ?),0) AS refundAmount");
        sql.append(",IFNULL((SELECT SUM(amount) FROM  erp_funds_detail WHERE `source`='YONGJIN' AND billDate= ?),0) AS yongjin");
        sql.append(",IFNULL((SELECT SUM(amount) FROM  erp_funds_detail WHERE `source`='YINGXIAO' AND billDate= ?),0) AS yingxiao");
        sql.append(",IFNULL((SELECT SUM(amount) FROM  erp_funds_detail WHERE `source`='SALEFEE' AND billDate= ?),0) AS saleFeeOther;");
        List<LedgerDayModel> list = new ArrayList<>();
       

        for(int i=0;i<=days;i++){
            Calendar sDate = Calendar.getInstance();
            sDate.setTime(startDate2);
            sDate.add(Calendar.DATE, +i); //得到前一天
            Date date = sDate.getTime();

            String dayStr = DateUtil.dateToString(date, "yyyy-MM-dd");
            List<Object> params = new ArrayList<>();
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            params.add(dayStr);
            LedgerDayModel day = jdbcTemplate.queryForObject(sql.toString(),new BeanPropertyRowMapper<>(LedgerDayModel.class),params.toArray(new Object[params.size()]));
            list.add(0,day);
        }
        
        return list;
    }
}
